﻿using System;
using System.Collections.Generic;
using System.Text;
using CFSystem.JXC.IDAL;
using CFSystem.JXC.Model;
using System.Data;
using System.Data.OleDb;
using DBUtility;

namespace CFSystem.JXC.AccessDAL
{
    public class Vendor : IVendor
    {

        #region IVendor Members

        IList<VendorInfo> IVendor.GetVendors()
        {
            string sqlSelect = "SELECT * from tb_Vendor";

            return GetVendors(sqlSelect);

        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        IList<VendorInfo> GetVendors(string sqlSelect)
        {
            OleDbDataReader dr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, sqlSelect);

            IList<VendorInfo> UnitItems = new List<VendorInfo>();

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    UnitItems.Add(GetVendorInfo(dr));
                }
                dr.Close();
            }
            return UnitItems;
        }
        /// <summary>
        /// 
        /// </summary>
        private VendorInfo GetVendorInfo(IDataReader datareader)
        {
            VendorInfo vi = new VendorInfo();
            vi.VendorID = Convert.ToInt32(datareader["ID"]);
            vi.VendorName = datareader["Name"].ToString();
            vi.PhoneNo = datareader["PhoneNumber"].ToString();
            vi.Contact = datareader["Contact"].ToString();
            vi.Address = datareader["Address"].ToString();
            vi.Account = datareader["Account"].ToString();
            vi.Email = datareader["Mail"].ToString();
            vi.Memo = datareader["Memo"].ToString();
            return vi;
        }

        VendorInfo IVendor.GetVendor(int VendorID)
        {
            string sqlSelect = "select * from [tb_Vendor] where [ID]=" + VendorID;
            OleDbDataReader dr = AccessHelper.ExecuteReader(AccessHelper.ConnectionString, CommandType.Text, sqlSelect);

            VendorInfo UnitItems = new VendorInfo();

            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    UnitItems = GetVendorInfo(dr);
                    return UnitItems;
                }
                dr.Close();
            }

            return null;

        }
        /// <summary>
        /// INSERT VENDOR 
        /// </summary>
        /// <param name="vendor"></param>
        void IVendor.InsertVendor(VendorInfo vendor)
        {

            string sqlInsert = "insert into [tb_Vendor]([Name],[Contact],[PhoneNumber],[Mail],[Address],[Account],[Memo]) values('" + vendor.VendorName + "', '"
                                            + vendor.Contact + "', '"
                                            + vendor.PhoneNo + "','"
                                            + vendor.Email + "', '"
                                            + vendor.Address + "', '"
                                            + vendor.Account + "','"
                                            + vendor.Memo + "' )";

            int returnCnt = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, sqlInsert);

        }
        /// <summary>
        /// DEL vendor
        /// </summary>
        /// <param name="VendorID"></param>
        void IVendor.DelVendorByID(int VendorID)
        {
            string sqlDelete = "DELETE FROM [tb_Vendor] WHERE [ID] =" + VendorID;
            int resultCnt = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, sqlDelete);

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="vendor"></param>
        void IVendor.UpdateVendorByID(VendorInfo vendor)
        {
            string sqlUpdate = "UPDATE [tb_Vendor] SET [Name] = '" + vendor.VendorName
                             + "', [Contact] = '" + vendor.Contact
                             + "', [PhoneNumber] = '" + vendor.PhoneNo
                             + "', [Mail] = '" + vendor.Email
                             + "', [address] = '" + vendor.Address
                             + "', [account] = '" + vendor.Account
                             + "', [memo] = '" + vendor.Memo
                             + "' WHERE [ID] = " + vendor.VendorID;
            int resultCnt = AccessHelper.ExecuteNonQuery(AccessHelper.ConnectionString, CommandType.Text, sqlUpdate);
        }

        #endregion
    }
}
